package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Referral;

public class DBReferralDAO implements ReferralDAO {
	public static final org.slf4j.Logger LOG = LoggerFactory
			.getLogger(DBReferralDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;
	private PreparedStatement updateStmt;
	private PreparedStatement delStmt;

	protected DBReferralDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	@Override
	public Referral saveReferral(Referral referral) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			saveStmt = conn
					.prepareStatement("INSERT INTO TALENT_REFERRAL (referral_ID,talent_ID, referral_comment, relationship, is_primary, is_delete) VALUES (?,?,?,?,?,?)");
			saveStmt.setInt(1, referral.getReferralId());
			saveStmt.setInt(2, referral.getTalentId());
			saveStmt.setString(3, referral.getComment());
			saveStmt.setString(4, referral.getRelationship());
			saveStmt.setBoolean(5, referral.getIsPrimary());
			saveStmt.setBoolean(6, referral.getIsDelete());

			saveStmt.executeUpdate();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("ERROR saving referral", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return referral;
	}

	@Override
	public Collection<Referral> loadReferral(Integer talentId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Collection<Referral> referrals = new Vector<Referral>();
		try {
			loadStmt = conn
					.prepareStatement("SELECT referral_ID, talent_ID, referral_comment, relationship, is_primary FROM TALENT_REFERRAL WHERE talent_ID=? and is_delete=false");
			loadStmt.setInt(1, talentId);
			ResultSet rs = loadStmt.executeQuery();
			while (rs.next()) {
				Referral referral = new Referral();
				referral.setReferralId(rs.getInt("referral_ID"));
				referral.setTalentId(rs.getInt("talent_ID"));
				referral.setComment(rs.getString("referral_comment"));
				referral.setRelationship(rs.getString("relationship"));
				// referral.setIsDelete(rs.getBoolean("is_delete"));
				referral.setIsPrimary(rs.getBoolean("is_primary"));
				referrals.add(referral);
			}
			rs.close();
			loadStmt.close();
		} catch (Exception e) {
			LOG.error("Error loading referral by talent id", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}

		return referrals;
	}

	@Override
	public void updateReferral(Referral referral) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			updateStmt = conn
					.prepareStatement("UPDATE TALENT_REFERRAL SET referral_comment=?, relationship=?,"
							+ " is_primary=?, is_delete=? WHERE talent_ID=? and  referral_ID=?");

			updateStmt.setString(1, referral.getComment());
			updateStmt.setString(2, referral.getRelationship());
			updateStmt.setBoolean(3, referral.getIsPrimary());
			updateStmt.setBoolean(4, referral.getIsDelete());
			updateStmt.setInt(5, referral.getTalentId());
			updateStmt.setInt(6, referral.getReferralId());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading referrals by RID", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	@Override
	public void deleteReferral(Integer referralId, Integer talentId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			delStmt = conn
					.prepareStatement("UPDATE TALENT_REFERRAL SET is_delete=true where referral_ID=? AND talent_ID=?");
			delStmt.setInt(1, referralId);
			delStmt.setInt(2, talentId);
			delStmt.executeUpdate();
			delStmt.close();
		} catch (SQLException e) {
			LOG.error("Error deleting referral", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	@Override
	public Referral loadReferral(Integer referralId, Integer talentId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Referral referral = new Referral();
		try {
			loadStmt = conn
					.prepareStatement("SELECT referral_ID, talent_ID, referral_comment, relationship, is_primary FROM TALENT_REFERRAL WHERE referral_ID=? and talent_ID=? ");
			loadStmt.setInt(1, referralId);
			loadStmt.setInt(2, talentId);
			ResultSet rs = loadStmt.executeQuery();

			if (rs.next()) {
				referral.setReferralId(rs.getInt("referral_ID"));
				referral.setTalentId(rs.getInt("talent_ID"));
				referral.setComment(rs.getString("referral_comment"));
				referral.setRelationship(rs.getString("relationship"));
				referral.setIsDelete(rs.getBoolean("is_delete"));
				referral.setIsPrimary(rs.getBoolean("is_primary"));
			} else {
				referral = null;
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error deleting referral", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return referral;
	}

}
